--creation of studentType1 object 

create or replace type addressType as object(
street	varchar2(50),
city	varchar2(15),
state	char(2),
zip	varchar2(10));

create or replace type studenttype1 as object(
stuid		varchar2(6),
lastName	varchar2(20),
firstName	varchar2(20),
address		Addresstype,
advisor		varchar2(6),
credits		number(3),
dateOfBirth	date,
member function findAge return number,
member procedure addCredits(numCredits in number)
)
INSTANTIABLE
NOT FINAL;
/

--code for member methods for studenttype1 object 

create or replace type body studentType1 is

member function findAge return number is

age number;
begin
	
	age := extract(year from (sysdate-dateofBirth) year to month);
	return(age);
end; -- if findAge

member procedure addCredits(numCredits in number) is
begin
	self.credits := self.credits+numcredits;
end;--of addCredits
end;

/



-- creation of tables to test studenttype1 object and methods  
-- NewFaculty table is needed for foreign key in NewStudent table  

create table NewFaculty(
facid	varchar2(6) primary key,
facname	varchar2(20),
dept	varchar2(20),
rank	varchar2(15));

/* Here is an object table with embedded object, address   */
create table Newstudent of StudentType1
(CONSTRAINT Stu_stuid_pk PRIMARY KEY(stuid),
CONSTRAINT Stu_advisorId_fk FOREIGN KEY (advisor) REFERENCES NewFaculty(facid));

/* Use the constructors to input values   */
insert into newstudent values(studenttype1('S999','Fernandes','Luis',addresstype('101 Pine Lane',
'Madison','WI','53726'),null,0,'15-Jan-1990'));

/* select statement requires alias, but can use dot notation for attributes and methods  */
select s.stuid, s.address.street, s.findAge()
from newstudent s
where s.stuid='S999';

/* update requires alias to refer to attributes of embedded object    */
update NewStudent n
set dateOfBirth = '20-Jun-1990',n.address.zip='53727'
where stuid='S999';



